package com.syq.dao.impl;

import com.syq.bean.Student;
import com.syq.dao.StudentDao;
import com.syq.util.DBUtils;
import com.syq.util.StudentEnum;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class StudentDaoImpl extends DBUtils implements StudentDao {
    @Override
    public List<Student> getStudents(String stuName,String stuNo,int sex,int pageIndex,int pageSize) {
        List<Student> students = new ArrayList<>();
        List parms = new ArrayList();
        try {
            StringBuffer sql = new StringBuffer("select * from student where 1=1 and state!=4");
            if(stuName!=null && stuName.length()>0) {
                sql.append(" and stuname like ?");
                parms.add("%"+stuName+"%");
            }
            if(stuNo!=null&&stuNo.length()>0) {
                sql.append(" and stuno=?");
                parms.add(stuNo);
            }
            if(sex!=-1) {
                sql.append(" and sex=?");
                parms.add(sex);
            }
            //分页 limit (pageIndex-1)*pagesize,pagesize
            sql.append(" limit ?,?");
            parms.add((pageIndex-1)*pageSize);
            parms.add(pageSize);

            resultSet = query(sql.toString(),parms);
            while (resultSet.next()) {
                Student student = new Student();
                student.setStuId(resultSet.getInt("stuid"));
                student.setStuNo(resultSet.getString("stuno"));
                student.setStuName(resultSet.getString("stuname"));
                student.setSex(resultSet.getInt("sex"));
                student.setPhone(resultSet.getString("phone"));
                student.setProfession(resultSet.getString("profession"));
                student.setRegDate(resultSet.getDate("regdate"));
                student.setEmail(resultSet.getString("email"));
                student.setRegistered(resultSet.getString("registered"));
                student.setAddress(resultSet.getString("address"));
                student.setIdNumber(resultSet.getString("idnumber"));
                student.setPolitics(resultSet.getString("politics"));
                student.setState(resultSet.getInt("state"));
                student.setGid(resultSet.getInt("gid"));
                student.setIntrodction(resultSet.getString("introduction"));
                students.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return students;
    }

    @Override
    public int total(String stuName, String stuNo, int sex) {
        List parms = new ArrayList();
        int total = 0;
        try {
            StringBuffer sql = new StringBuffer("select count(*) from student where 1=1 and state!=4 ");
            if(stuName!=null && stuName.length()>0) {
                sql.append(" and stuname like ?");
                parms.add("%"+stuName+"%");
            }
            if(stuNo!=null&&stuNo.length()>0) {
                sql.append(" and stuno=?");
                parms.add(stuNo);
            }
            if(sex!=-1) {
                sql.append(" and sex=?");
                parms.add(sex);
            }

            resultSet = query(sql.toString(), parms);
            while (resultSet.next()) {
                total = resultSet.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeAll();
        }
        return total;
    }

    @Override
    public int insertStu(Student student) {
        int i = 0;
        try {
            String sql = "insert into student values(null,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            List params = new ArrayList();
            params.add(student.getStuName());
            params.add(student.getStuNo());
            params.add(student.getSex());
            params.add(student.getPhone());
            params.add(student.getEmail());
            params.add(student.getRegistered());
            params.add(student.getAddress());
            params.add(student.getProfession());
            params.add(student.getIdNumber());
            params.add(student.getPolitics());
            params.add(new Date());
            params.add(StudentEnum.READING.type);
            params.add(student.getIntrodction());
            params.add(student.getGid());
            i = update(sql,params);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            closeAll();
        }
        return i;
    }

    @Override
    public Student findById(int sid) {
        String sql = "select * from student where stuid=?";
        List params = new ArrayList();
        Student student = new Student();
        try {
            params.add(sid);
            resultSet = query(sql,params);
            while (resultSet.next()) {
                student.setStuId(resultSet.getInt("stuid"));
                student.setStuNo(resultSet.getString("stuno"));
                student.setStuName(resultSet.getString("stuname"));
                student.setSex(resultSet.getInt("sex"));
                student.setPhone(resultSet.getString("phone"));
                student.setProfession(resultSet.getString("profession"));
                student.setRegDate(resultSet.getDate("regdate"));
                student.setEmail(resultSet.getString("email"));
                student.setRegistered(resultSet.getString("registered"));
                student.setAddress(resultSet.getString("address"));
                student.setIdNumber(resultSet.getString("idnumber"));
                student.setPolitics(resultSet.getString("politics"));
                student.setState(resultSet.getInt("state"));
                student.setGid(resultSet.getInt("gid"));
                student.setIntrodction(resultSet.getString("introduction"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return student;
    }

    @Override
    public int updateStu(Student student) {
        String sql = "update student set stuno=?,stuname=?,sex=?,phone=?,profession=?,email=?,registered=?,address=?,idnumber=?,politics=?,gid=?,introduction=? where stuid=?";
        List params = new ArrayList();

        params.add(student.getStuNo());
        params.add(student.getStuName());
        params.add(student.getSex());
        params.add(student.getPhone());
        params.add(student.getProfession());
        params.add(student.getEmail());
        params.add(student.getRegistered());
        params.add(student.getAddress());
        params.add(student.getIdNumber());
        params.add(student.getPolitics());
        params.add(student.getGid());
        params.add(student.getIntrodction());
        params.add(student.getStuId());
        int count = update(sql,params);
        return count;
    }

    @Override
    public int delStu(String sid) {
        int count =0;
        try {
            String sql = "update student set state=? where stuid=?";
            List params = new ArrayList();
            params.add(StudentEnum.DELETE.type);
            params.add(sid);
            count = update(sql,params);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            closeAll();
        }

        return count;
    }

    @Override
    public Student finByNo(String sno) {

        String sql = "select * from student where stuno=?";
        List params = new ArrayList();
        Student student =null ;
        try {
            params.add(sno);
            resultSet = query(sql,params);
            while (resultSet.next()) {
                student = new Student();
                student.setStuId(resultSet.getInt("stuid"));
                student.setStuNo(resultSet.getString("stuno"));
                student.setStuName(resultSet.getString("stuname"));
                student.setSex(resultSet.getInt("sex"));
                student.setPhone(resultSet.getString("phone"));
                student.setProfession(resultSet.getString("profession"));
                student.setRegDate(resultSet.getDate("regdate"));
                student.setEmail(resultSet.getString("email"));
                student.setRegistered(resultSet.getString("registered"));
                student.setAddress(resultSet.getString("address"));
                student.setIdNumber(resultSet.getString("idnumber"));
                student.setPolitics(resultSet.getString("politics"));
                student.setState(resultSet.getInt("state"));
                student.setGid(resultSet.getInt("gid"));
                student.setIntrodction(resultSet.getString("introduction"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return student;
    }
}
